package tms.sync.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import tms.sync.dao.TmsSyncRealDao;

public class TmsSyncRealDaoImpl implements TmsSyncRealDao{
	
	private static TmsSyncRealDaoImpl tmsSyncEXclassDaoImpl;
	
	public static TmsSyncRealDaoImpl getInstance(){
		if(tmsSyncEXclassDaoImpl == null){
			tmsSyncEXclassDaoImpl = new TmsSyncRealDaoImpl();
		}
		return tmsSyncEXclassDaoImpl;
	}
	
	public TmsSyncRealDaoImpl() {
		init();
	}

	private void init(){
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			System.out.println("[ LOG ] : DRIVER LOADING SUCCESS" + e);
		}
	}
	
	private Connection getConnection() throws SQLException{
		Connection conn = null;
		String url = "jdbc:oracle:thin:@134.75.122.30:1521/hoseo";
		String id = "HIS";
		String psw = "gkrtktkdydwk!";
		conn = DriverManager.getConnection(url, id, psw);
		return conn;
	}
	
	private void close(Connection conn, Statement stmt, ResultSet rs){
		if(rs != null){
			try {
				rs.close();
			} catch (SQLException e) {
			}
		}
		if(stmt != null){
			try {
				stmt.close();
			} catch (SQLException e) {
			}
		}
		if(conn != null){
			try {
				conn.close();
			} catch (SQLException e) {
			}
		}
	}

	@Override
	public List<?> getUserInfo(String strUserId) {
		List listResult = new ArrayList();
		Connection conn = null;
		PreparedStatement psmt = null;
		ResultSet rs = null;
//		String sql = "SELECT USER_ID, USER_NM FROM CMN_USER_ID WHERE USER_ID = ?";
		String sql = "SELECT DEPT_CD, DEPT_NM, UP_CD, DEPT_DIV, USE_YN, DEPT_NM_ENG, ALIAS, PRT_ORD, CRE_DT, ABO_DT, CRT_DTTM, CRT_ID, CRT_PGM_ID, CRT_IP FROM CMN_DEPT_CD";
		
		String _pre = "HS_DEPT_";
		int vCnt = 1;
		
		try {
			conn = getConnection();
			System.out.println("[ LOG ] : DB CONNECTION SUCCESS");
			
			psmt = conn.prepareStatement(sql);
//			psmt.setString(1, strUserId);
			
			rs = psmt.executeQuery();
			System.out.println("[ LOG ] : SQL START");
			
			while(rs.next()){
				int i = 1;
				Map<String, Object> mapResult = new HashMap<String, Object>();
//				mapResult.put("USER_ID", rs.getString(i++));
				mapResult.put("DEPT_CD", rs.getString(i++));
				mapResult.put("DEPT_NM", rs.getString(i++));
				String voDicEst = _pre + vCnt++;
				mapResult.put("DIC_EST", voDicEst);
				
				listResult.add(mapResult);
			}
			System.out.println("[ LOG ] : SQL RESULT LIST = "+listResult);
			
		} catch (SQLException e) {
			System.out.println("[ LOG ] : DB CONNECTION FAIL : "+e);
		}finally{
			close(conn, psmt, rs);
			System.out.println("[ LOG ] : END");
		}
		return listResult;
	}
	
	// 부서 코드 조회
	@Override 
	public List<?> getDeptInfo(Map strMap) {
		List listResult = new ArrayList();
		Connection conn = null;
		PreparedStatement psmt = null;
		ResultSet rs = null;
		String sql = " SELECT DEPT_CD, DEPT_NM, UP_CD, DEPT_DIV, USE_YN, DEPT_NM_ENG, ALIAS, PRT_ORD, CRE_DT, ABO_DT, CRT_DTTM, CRT_ID, CRT_PGM_ID, CRT_IP " +
				"FROM CMN_DEPT_CD " +
				"WHERE NVL(UPD_DTTM, TO_DATE('19000101', 'YYYYMMDD')) >= TO_DATE(?, 'YYYYMMDD')";
		
		String _pre = "HS_DEPT_";
		int vCnt = Integer.parseInt(strMap.get("MAX_CNT").toString());
		
		try {
			conn = getConnection();
			System.out.println("[ LOG ] : DB CONNECTION SUCCESS");
			
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, strMap.get("MAX_DATE").toString());
			rs = psmt.executeQuery();
			
			while(rs.next()){
				int i = 1;
				Map<String, Object> mapResult = new HashMap<String, Object>();
				mapResult.put("DEPT_CD", rs.getString(i++));
				mapResult.put("DEPT_NM", rs.getString(i++));
				mapResult.put("UP_CD", rs.getString(i++));
				mapResult.put("DEPT_DIV", rs.getString(i++));
				mapResult.put("USE_YN", rs.getString(i++));
				mapResult.put("DEPT_NM_ENG", rs.getString(i++));
				mapResult.put("ALIAS", rs.getString(i++));
				mapResult.put("PRT_ORD", rs.getString(i++));
				mapResult.put("CRE_DT", rs.getString(i++));
				mapResult.put("ABO_DT", rs.getString(i++));
				mapResult.put("CRT_DTTM", rs.getString(i++));
				mapResult.put("CRT_ID", rs.getString(i++));
				mapResult.put("CRT_PGM_ID", rs.getString(i++));
				mapResult.put("CRT_IP", rs.getString(i++));
				
				String voDicEst = "" + _pre + vCnt++;
				mapResult.put("DIC_EST", voDicEst);
				
				listResult.add(mapResult);
			}
		} catch (SQLException e) {
			System.out.println("[ LOG ] : DB CONNECTION FAIL : "+e);
		}finally{
			close(conn, psmt, rs);
			System.out.println("[ LOG ] : END");
		}
		return listResult;
	}

}

